﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace VentHost.Lib
{
    public class DB : IDisposable
    {
        #region  Properties
        /// <summary>
        /// SQL Connection String
        /// </summary>
        /// <value></value>
        /// <returns></returns>
        /// <remarks></remarks>
        public string ConnectionString
        {
            get
            {
                return "Server=localhost;Database=VentHost;Uid=VentHostUser;Pwd=VentHostUser;";
            }
        }

        /// <summary>
        /// SQL Connection State
        /// </summary>
        /// <value></value>
        /// <returns></returns>
        /// <remarks></remarks>
        public ConnectionState ConnectionState
        {
            get
            {
                if (Conn == null)
                {
                    return ConnectionState.Closed;
                }
                else
                {
                    return Conn.State;
                }
            }
        }

        #endregion

        #region  Private Vars
        private SqlConnection Conn;
        private SqlCommand Cmd;
        #endregion

        #region  Database Connectivity
        /// <summary>
        /// Initializes and opens the database connection
        /// </summary>
        /// <returns></returns>
        private bool OpenDB()
        {
            bool _Return;
            try
            {
                if (Conn == null)
                {
                    Conn = new SqlConnection();
                }

                if (Conn.State == ConnectionState.Open)
                {
                    Conn.ConnectionString = ConnectionString;
                    Conn.Open();
                }

                _Return = true;
            }
            catch (Exception ex)
            {
                _Return = false;
                throw new Exception(ex.Message, ex.InnerException);
            }
            return _Return;
        }

        /// <summary>
        /// Closes the database connection
        /// </summary>
        /// <returns></returns>
        private bool CloseDB()
        {
            bool _Return;
            try
            {
                if (Conn == null)
                {
                    if (Conn.State == ConnectionState.Open)
                    {
                        Conn.Close();
                    }
                }
                _Return = true;
            }
            catch (Exception ex)
            {
                _Return = false;
                throw new Exception(ex.Message, ex.InnerException);
            }
            return _Return;
        }

        /// <summary>
        /// Returns the database connection
        /// </summary>
        /// <returns></returns>
        public SqlConnection GetConnection()
        {
            try
            {
                if (OpenDB() == false)
                {
                    return null;
                }
                else
                {
                    return Conn;
                }
            }
            catch (Exception ex)
            {
                return null;
                throw new Exception(ex.Message, ex.InnerException);
            }
        }

        #endregion

        #region  Database Command
        /// <summary>
        /// Initializes and opens the database command
        /// </summary>
        /// <param name="CommandText"></param>
        /// <param name="CommandType"></param>
        /// <param name="Parameters"></param>
        /// <returns></returns>
        private bool OpenCommand(string CommandText, CommandType CommandType = CommandType.Text, List<IDataParameter> Parameters = null)
        {
            bool _Return;
            try
            {
                if (OpenDB() == true)
                {
                    if (Cmd == null)
                    {
                        Cmd = new SqlCommand();
                    }

                    Cmd.Connection = Conn;
                    Cmd.CommandText = CommandText;
                    Cmd.CommandType = CommandType;

                    if (Parameters == null)
                    {
                        Cmd.Parameters.Clear();

                        foreach (IDataParameter param in Parameters)
                        {
                            Cmd.Parameters.Add(param);
                        }
                    }
                    _Return = true;
                }
                else
                {
                    _Return = false;
                }
            }
            catch (Exception ex)
            {
                _Return = false;
                throw new Exception(ex.Message, ex.InnerException);
            }
            return _Return;
        }

        /// <summary>
        /// Closes the database command
        /// </summary>
        /// <returns></returns>
        private bool CloseCommand()
        {
            bool _Return;
            try
            {
                if (Cmd == null)
                {
                    Cmd.Dispose();
                    Cmd = null;
                }
                _Return = true;
            }
            catch (Exception ex)
            {
                _Return = false;
                throw new Exception(ex.Message, ex.InnerException);
            }
            return _Return;
        }

        /// <summary>
        /// Returns the database command using a specified query
        /// </summary>
        /// <param name="CommandText"></param>
        /// <param name="CommandType"></param>
        /// <param name="Parameters"></param>
        /// <returns></returns>
        public SqlCommand GetCommand(string CommandText, CommandType CommandType = CommandType.Text, List<IDataParameter> Parameters = null)
        {
            try
            {
                if (OpenDB() == false)
                {
                    return null;
                }
                else
                {
                    if (OpenCommand(CommandText, CommandType, Parameters) == false)
                    {
                        return null;
                    }
                    else
                    {
                        return Cmd;
                    }
                }
            }
            catch (Exception ex)
            {
                return null;
                throw new Exception(ex.Message, ex.InnerException);
            }
        }

        /// <summary>
        /// Returns a data reader using a specified query
        /// </summary>
        /// <param name="CommandText"></param>
        /// <param name="CommandType"></param>
        /// <param name="Parameters"></param>
        /// <returns></returns>
        public SqlDataReader GetDataReader(string Query, CommandType CommandType = CommandType.Text, List<IDataParameter> Parameters = null)
        {
            SqlDataReader dr;
            try
            {
                if (OpenDB() == false)
                {
                    return null;
                }
                else
                {
                    if (OpenCommand(Query, CommandType, Parameters) == false)
                    {
                        return null;
                    }
                    else
                    {
                        dr = Cmd.ExecuteReader();
                        return dr;
                    }
                }
            }
            catch (Exception ex)
            {
                return null;
                throw new Exception(ex.Message, ex.InnerException);
            }
        }

        /// <summary>
        /// Returns a data view using a specified query
        /// </summary>
        /// <param name="CommandText"></param>
        /// <param name="CommandType"></param>
        /// <param name="Parameters"></param>
        /// <returns></returns>
        public DataView GetDataView(string CommandText, CommandType CommandType = CommandType.Text, List<IDataParameter> Parameters = null)
        {
            SqlDataAdapter da;
            DataSet ds;
            DataView dv;
            try
            {
                if (OpenDB() == false)
                {
                    return null;
                }
                else
                {
                    if (OpenCommand(CommandText, CommandType, Parameters) == false)
                    {
                        return null;
                    }
                    else
                    {
                        da = new SqlDataAdapter(Cmd);
                        ds = new DataSet();
                        da.Fill(ds);
                        dv = new DataView(ds.Tables[0]);

                        return dv;
                    }
                }
            }
            catch (Exception ex)
            {
                return null;
                throw new Exception(ex.Message, ex.InnerException);
            }
        }

        /// <summary>
        /// Executes a database command and returns the number of rows affected
        /// </summary>
        /// <param name="CommandText"></param>
        /// <param name="CommandType"></param>
        /// <param name="Parameters"></param>
        /// <returns></returns>
        public int ExecuteCommand(string CommandText, CommandType CommandType = CommandType.Text, List<IDataParameter> Parameters = null)
        {
            int _Return;
            try
            {
                if (OpenDB() == false)
                {
                    _Return = 0;
                }
                else
                {
                    if (OpenCommand(CommandText, CommandType, Parameters) == false)
                    {
                        _Return = 0;
                    }
                    else
                    {
                        _Return = Cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                _Return = 0;
                throw new Exception(ex.Message, ex.InnerException);
            }
            return _Return;
        }

        /// <summary>
        /// Queries a database using a specified query. Returns the first column in the first row.
        /// </summary>
        /// <param name="CommandText"></param>
        /// <param name="CommandType"></param>
        /// <param name="Parameters"></param>
        /// <returns></returns>
        public string ExecuteScalar(string CommandText, CommandType CommandType = CommandType.Text, List<IDataParameter> Parameters = null)
        {
            string _Return;
            try
            {
                if (OpenDB() == false)
                {
                    _Return = "";
                }
                else
                {
                    if (OpenCommand(CommandText, CommandType, Parameters) == false)
                    {
                        _Return = "";
                    }
                    else
                    {
                        _Return = Cmd.ExecuteScalar().ToString();

                        if (_Return == null)
                        {
                            _Return = "";
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                _Return = "";
                throw new Exception(ex.Message, ex.InnerException);
            }
            return _Return;
        }

        #endregion

        #region IDisposable Support
        private bool disposedValue;

        protected virtual void Dispose(bool disposing)
        {
            if (!this.disposedValue)
            {
                if (disposing)
                {
                    CloseCommand();
                    CloseDB();
                }
            }
            this.disposedValue = true;
        }

        public virtual void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
        #endregion
    }
}
